﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Logistics.DataAccess
{
    public class old_ExportData
    {
        /// <summary>
        /// 准备导出前的数据
        /// </summary>
        public void old_PrepareData(string l_tc_owr009)
        {
            var strSql = new StringBuilder();
            strSql.AppendLine("UPDATE edi_test.tc_ows_file SET tc_ows022 = '9999999999' ");
            strSql.AppendLine("WHERE tc_ows022 IS null ");
            strSql.AppendLine("AND tc_ows000|| tc_ows001 IN");
            strSql.AppendLine("(SELECT tc_owr001 || tc_owr002 FROM edi_test.tc_owr_file WHERE tc_owr025 <> '4' AND");
            strSql.AppendLine("tc_owr019 = '1' AND tc_owr009 = '" + l_tc_owr009.ToString() + "')");
            DBHelper.ExecuteSql(strSql.ToString());

            strSql.Clear();
            strSql.AppendLine("UPDATE edi_test.tc_ows_file SET tc_ows022 = '999999' || tc_ows003");
            strSql.AppendLine("WHERE tc_ows003 like '%0' AND tc_ows022 is null");
            strSql.AppendLine("AND tc_ows000|| tc_ows001 IN");
            strSql.AppendLine("(SELECT tc_owr001 || tc_owr002 FROM edi_test.tc_owr_file WHERE tc_owr025 = '4' AND");
            strSql.AppendLine("tc_owr019 = '1' AND tc_owr009 = '" + l_tc_owr009.ToString() + "')");
            DBHelper.ExecuteSql(strSql.ToString());

            strSql.Clear();
            strSql.AppendLine("UPDATE edi_test.tc_ows_file SET tc_ows022 = '99999999' || substr(tc_ows003, 1, 2)");
            strSql.AppendLine("WHERE tc_ows003 NOT like '%0' AND tc_ows022 is null");
            strSql.AppendLine("AND tc_ows000|| tc_ows001 IN");
            strSql.AppendLine("(SELECT tc_owr001 || tc_owr002 FROM edi_test.tc_owr_file WHERE tc_owr025 = '4' AND");
            strSql.AppendLine("tc_owr019 = '1' AND tc_owr009 = '" + l_tc_owr009.ToString() + "')");
            DBHelper.ExecuteSql(strSql.ToString());

            strSql.Clear();
            //更新tc_ows003
            strSql.AppendLine("UPDATE edi_test.tc_ows_file A SET tc_ows003 = (select DISTINCT tc_owi009 from ");
            strSql.AppendLine("edi_test.tc_owi_file where tc_owi001 = (select tc_owr009 from edi_test.tc_owr_file ");
            strSql.AppendLine("WHERE tc_owr001 = A.tc_ows000 AND tc_owr002 = A.tc_ows001  AND tc_owr009 = '" + l_tc_owr009.ToString() + "')) ");
            strSql.AppendLine("WHERE(tc_ows003 is null OR tc_ows003 = '' OR tc_ows003 = ' ')");
            DBHelper.ExecuteSql(strSql.ToString());

            //更新一些数据
            strSql.Clear();
            strSql.AppendLine("UPDATE edi_test.tc_ows_file SET tc_ows003 = SUBSTR(tc_ows003, 1, 2)||'Z'||SUBSTR(tc_ows003, 4, 1)");
            strSql.AppendLine(" WHERE SUBSTR(tc_ows003, 1, 2)='42' ");
            strSql.AppendLine(" AND tc_ows000|| tc_ows001 IN");
            strSql.AppendLine("(SELECT tc_owr001 || tc_owr002 FROM edi_test.tc_owr_file WHERE ");
            strSql.AppendLine(" tc_owr019 = '1' AND tc_owr009 = '" + l_tc_owr009.ToString() + "')");
            DBHelper.ExecuteSql(strSql.ToString());

            //更新一些数据
            strSql.Clear();
            strSql.AppendLine("UPDATE edi_test.tc_ows_file SET tc_ows003='42Z9' ");
            strSql.AppendLine("WHERE tc_ows003='42Z8' ");
            strSql.AppendLine(" AND tc_ows000|| tc_ows001 IN");
            strSql.AppendLine("(SELECT tc_owr001 || tc_owr002 FROM edi_test.tc_owr_file WHERE ");
            strSql.AppendLine("tc_owr019 = '1' AND tc_owr009 = '" + l_tc_owr009.ToString() + "')");
            DBHelper.ExecuteSql(strSql.ToString());

            //更新一些数据
            strSql.Clear();
            strSql.AppendLine("UPDATE edi_test.tc_ows_file SET tc_ows003='42Z1' ");
            strSql.AppendLine("WHERE tc_ows003='42Z0' ");
            strSql.AppendLine(" AND tc_ows000|| tc_ows001 IN");
            strSql.AppendLine("(SELECT tc_owr001 || tc_owr002 FROM edi_test.tc_owr_file WHERE ");
            strSql.AppendLine("tc_owr019 = '1' AND tc_owr009 = '" + l_tc_owr009.ToString() + "')");
            DBHelper.ExecuteSql(strSql.ToString());


            //更新一些数据
            strSql.Clear();
            strSql.AppendLine("UPDATE edi_test.tc_ows_file SET tc_ows004='A4' ");
            strSql.AppendLine("WHERE tc_ows004='ZTNN'");
            strSql.AppendLine("AND tc_ows000|| tc_ows001 IN");
            strSql.AppendLine("(SELECT tc_owr001 || tc_owr002 FROM edi_test.tc_owr_file,edi_test.tc_owi_file ");
            strSql.AppendLine(" WHERE tc_owr019 = '1' AND tc_owr009 = '" + l_tc_owr009.ToString() + "')");
            DBHelper.ExecuteSql(strSql.ToString());

            //更新一些数据
            strSql.Clear();
            strSql.AppendLine("UPDATE edi_test.tc_ows_file SET tc_ows004='A1' ");
            strSql.AppendLine("WHERE tc_ows004<>'ZTNN'");
            strSql.AppendLine("AND tc_ows000|| tc_ows001 IN");
            strSql.AppendLine("(SELECT tc_owr001 || tc_owr002 FROM edi_test.tc_owr_file,edi_test.tc_owi_file ");
            strSql.AppendLine(" WHERE tc_owi001 = tc_owr009 AND tc_owi007 = '1' "); //老物流
            strSql.AppendLine(" AND tc_owr019 = '1' AND tc_owr009 = '" + l_tc_owr009.ToString() + "')");
            DBHelper.ExecuteSql(strSql.ToString());

        }

        /// <summary>
        /// 1.单据传输资料主档
        /// </summary>
        /// <param name="tc_owr009">物流公司代码</param>
        /// <param name="tc_owr002">单据编号</param>
        /// <param name="tc_owr006">单据日期</param>
        /// <returns></returns>
        public List<Model.old_OwrModel> old_PrepareOwrData(string tc_owr009, string tc_owr002, string tc_owr006, string tc_owr007)
        {
            var strSql = new StringBuilder();
            strSql.AppendLine("SELECT tc_owr001,tc_owr002,tc_owr003,tc_owr005,tc_owr006,tc_owr007,tc_owr009,tc_owr013,tc_owr0131,");
            strSql.AppendLine(" tc_owr014,tc_owr015,tc_owr016,tc_owr017,tc_owr018,");
            strSql.AppendLine(" tc_ows002,tc_ows003,tc_ows004,tc_ows005,tc_ows006,tc_ows007,tc_ows008,");
            strSql.AppendLine(" tc_ows010,tc_ows011");
            strSql.AppendLine(" FROM  edi_test.tc_owr_file,edi_test.tc_ows_file");
            strSql.AppendLine(" WHERE tc_owr001=tc_ows000 AND tc_owr002 = tc_ows001");
            strSql.AppendFormat("  AND tc_owr009 = '{0}'", tc_owr009);
            strSql.AppendLine(" AND tc_owr019 = '1'");
            if (!string.IsNullOrWhiteSpace(tc_owr002))
                strSql.AppendFormat("AND tc_owr002 =  '{0}'", tc_owr002);
            if (!string.IsNullOrWhiteSpace(tc_owr006))
                strSql.AppendFormat("AND tc_owr006 = '{0}'", tc_owr006);
            if (!string.IsNullOrWhiteSpace(tc_owr007))
                strSql.AppendFormat("AND tc_owr007 = '{0}'", tc_owr007);
            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString()))
            {
                List<Model.old_OwrModel> lst = new List<Model.old_OwrModel>();
                while (dr.Read())
                {
                    var model = new Model.old_OwrModel();
                    model.tc_owr001 = DBHelper.GetString(dr["tc_owr001"]);//异动类型
                    StringBuilder sb = new StringBuilder();
                    sb.AppendFormat(" select DISTINCT owr004  from edi_test.owr_file where owr002 = '{0}' ", model.tc_owr001);
                    string l_tc_owr001 = DBHelper.GetSingle(sb.ToString()).ToString();
                    model.tc_owr001 = l_tc_owr001;
                    model.tc_owr002 = DBHelper.GetString(dr["tc_owr002"]);//单据编号
                    model.tc_ows002 = DBHelper.GetString(dr["tc_ows002"]); //单据项次

                    model.tc_owr005 = DBHelper.GetString(dr["tc_owr005"]);//客户编号
                    model.tc_owr006 = DBHelper.GetDateTime(dr["tc_owr006"]).Value.ToString("yyyyMMdd");//单据日期
                    model.tc_owr007 = DBHelper.GetDateTime(dr["tc_owr007"]).Value.ToString("yyyyMMdd");//发货日期
                    model.tc_owr009 = DBHelper.GetString(dr["tc_owr009"]);//物流公司代号
                    model.tc_ows003 = DBHelper.GetString(dr["tc_ows003"]);//仓库
                    model.l_temp_add = "0001";
                    model.tc_ows004 = DBHelper.GetString(dr["tc_ows004"]);//单身明细异动原因
                    model.tc_ows005 = DBHelper.GetString(dr["tc_ows005"]);//料号
                    model.tc_ows006 = DBHelper.GetString(dr["tc_ows006"]);//单位
                    model.tc_ows007 = DBHelper.GetDecimal(dr["tc_ows007"]);//数量
                    model.tc_ows008 = DBHelper.GetDecimal(dr["tc_ows008"]);//单价 未税
                    model.tc_ows010 = DBHelper.GetDecimal(dr["tc_ows010"]);//未税金额
                    model.tc_owr003 = DBHelper.GetString(dr["tc_owr003"]);//备注 导出方帐套号
                    model.tc_owr0131 = DBHelper.GetString(dr["tc_owr0131"]);//原始单号
                    model.tc_owr013 = DBHelper.GetString(dr["tc_owr013"]);//原始单号
                    model.tc_owr014 = DBHelper.GetString(dr["tc_owr014"]);//制表人
                    model.tc_ows011 = DBHelper.GetDecimal(dr["tc_ows011"]);//含税
                    model.tc_ows011_010 = DBHelper.GetDecimal(dr["tc_ows011"])- DBHelper.GetDecimal(dr["tc_ows010"]);//含税
                    model.tc_owr016 = DBHelper.GetString(dr["tc_owr016"]);//memo1
                    model.tc_owr017 = DBHelper.GetString(dr["tc_owr017"]);//memo2
                    model.tc_owr018 = DBHelper.GetString(dr["tc_owr018"]);//memo3
                   
                    lst.Add(model);
                }
                return lst;
            }
        }

        /// <summary>
        /// 保存单据传输资料主档结果
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool old_SaveOwrData(Model.TC_OWF_FILE model)
        {
            var strSql = new StringBuilder();
            strSql.AppendLine("INSERT INTO ");
            strSql.AppendLine("edi_test.tc_owf_file(tc_owf001, tc_owf002, tc_owf004, tc_owf003, tc_owf005, tc_owfuser, tc_owfdate)");
            strSql.AppendLine($"VALUES('{model.TC_OWF001}', '{model.TC_OWF002}', '{model.TC_OWF004}', '{model.TC_OWF003}', '{model.TC_OWF005}', '{model.TC_OWFUSER}', to_date('{model.TC_OWFDATE}','yyyy-MM-dd'))");
            DBHelper.ExecuteSql(strSql.ToString());
            return true;
        }


        /// <summary>
        /// 2.单据传输资料折扣档
        /// </summary>
        /// <returns></returns>
        public List<Model.OwwModel> old_PrepareOwwData(string tc_owf001)
        {
            var strSql = new StringBuilder();
            strSql.AppendLine("SELECT distinct tc_oww000, tc_oww001, tc_oww002, tc_oww003, tc_oww004 ");
            strSql.AppendLine("FROM edi_test.tc_oww_file,edi_test.tc_owf_file ");
            strSql.AppendLine("WHERE tc_oww001 = tc_owf003");
            strSql.AppendLine("AND(tc_owf002 is null OR tc_owf002 = '' OR tc_owf002 = ' ') ");
            strSql.AppendLine($"AND tc_owf001 ='{tc_owf001}' ");
            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString()))
            {
                List<Model.OwwModel> lst = new List<Model.OwwModel>();
                while (dr.Read())
                {
                    var model = new Model.OwwModel();
                    model.TC_OWW000 = DBHelper.GetString(dr["tc_oww000"]);
                    model.TC_OWW001 = DBHelper.GetString(dr["tc_oww001"]);
                    model.TC_OWW002 = DBHelper.GetString(dr["tc_oww002"]);
                    model.TC_OWW003 = DBHelper.GetString(dr["tc_oww003"]);
                    model.TC_OWW004 = DBHelper.GetString(dr["tc_oww004"]);
                    lst.Add(model);
                }
                return lst;
            }
        }


        /// <summary>
        /// 3.客户基本资料档
        /// </summary>
        /// <returns></returns>
        public List<Model.OccModel> old_PrepareOccData(string tc_owf001)
        {
            var strSql = new StringBuilder();
            strSql.AppendLine("SELECT DISTINCT tc_owr009,occ01,occ18,ta_occ001,occ041,ta_occ011a,ta_occ012a,ta_occ006a,occ29,occ30,occ242,occ294,occ304,occ296,occdate ");
            strSql.AppendLine("FROM edi_test.occ_file,edi_test.tc_owr_file,edi_test.tc_owf_file ");
            strSql.AppendLine("WHERE occ01 = tc_owr005 AND tc_owr002 = tc_owf003");
            strSql.AppendLine("AND(tc_owf002 is null OR tc_owf002 = '' OR tc_owf002 = ' ') ");
            strSql.AppendLine($"AND tc_owf001 ='{tc_owf001}' ");
            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString()))
            {
                List<Model.OccModel> lst = new List<Model.OccModel>();
                while (dr.Read())
                {
                    var model = new Model.OccModel();
                    model.tc_owr009 = DBHelper.GetString(dr["tc_owr009"]);
                    model.occ01 = DBHelper.GetString(dr["occ01"]);
                    model.occ18 = DBHelper.GetString(dr["occ18"]);
                    model.ta_occ001 = DBHelper.GetString(dr["ta_occ001"]);
                    model.occ041 = DBHelper.GetString(dr["occ041"]);
                    model.ta_occ011a = DBHelper.GetString(dr["ta_occ011a"]);
                    model.ta_occ012a = DBHelper.GetString(dr["ta_occ012a"]);
                    model.ta_occ006a = DBHelper.GetString(dr["ta_occ006a"]);
                    model.occ29 = DBHelper.GetString(dr["occ29"]);
                    model.occ30 = DBHelper.GetString(dr["occ30"]);
                    model.occ294 = DBHelper.GetString(dr["occ294"]);
                    model.occ304 = DBHelper.GetString(dr["occ304"]);
                    model.occ296 = DBHelper.GetString(dr["occ296"]);
                    model.occdate = DBHelper.GetDateTime(dr["occdate"]).Value.ToString("yyyyMMdd");//日期

                    lst.Add(model);
                }
                return lst;
            }
        }

        /// <summary>
        /// 4.客户货号关系资料档
        /// </summary>
        /// <returns></returns>
        public List<Model.tc_obkModel> old_Preparetc_obkData(string tc_owf001)
        {
            var strSql = new StringBuilder();
            strSql.AppendLine("SELECT DISTINCT ima01,occ01,tc_obk03,tc_obk05,tc_obk06,tc_obk07");
            strSql.AppendLine("FROM edi_test.tc_owr_file,edi_test.tc_ows_file,edi_test.occ_file,edi_test.tc_obk_file,edi_test.ima_file,edi_test.tc_owf_file ");
            strSql.AppendLine("WHERE tc_owr005=occ01 AND tc_owr019 = '1' ");
            strSql.AppendLine("AND tc_obk01 = ima135 AND tc_obk02 = ta_occ021 ");
            strSql.AppendLine("AND tc_owr001=tc_ows000 AND tc_owr002 = tc_ows001  ");
            strSql.AppendLine("AND ima01 = tc_ows005 ");
            strSql.AppendLine("AND tc_owr002 = tc_owf003");
            strSql.AppendLine("AND(tc_owf002 is null OR tc_owf002 = '' OR tc_owf002 = ' ') ");
            strSql.AppendLine($"AND tc_owf001 ='{tc_owf001}' ");
            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString()))
            {
                List<Model.tc_obkModel> lst = new List<Model.tc_obkModel>();
                while (dr.Read())
                {
                    var model = new Model.tc_obkModel();
                    model.ima01 = DBHelper.GetString(dr["ima01"]);
                    model.occ01 = DBHelper.GetString(dr["occ01"]);
                    model.tc_obk03 = DBHelper.GetString(dr["tc_obk03"]);
                    model.tc_obk05 = DBHelper.GetString(dr["tc_obk05"]);
                    model.tc_obk06 = DBHelper.GetString(dr["tc_obk06"]);
                    model.tc_obk07 = DBHelper.GetString(dr["tc_obk07"]);
                    lst.Add(model);
                }
                return lst;
            }
        }




        /// <summary>
        /// 4.单据传输资料明细档
        /// </summary>
        /// <returns></returns>
        public List<Model.ImaModel> old_PrepareImaData(string tc_owf001)
        {
            var strSql = new StringBuilder();
            strSql.AppendLine("SELECT DISTINCT ima01,ta_ima001,ima02,ima31_fac,ima06,ima31,A.gfe02 ima31_dec,ima25,B.gfe02 ima25_dec,ima135, ");
            strSql.AppendLine("ta_ima027/1000 ta_ima027,ta_ima028,ta_ima024,ta_ima025,ta_ima026 ");
            strSql.AppendLine("FROM edi_test.ima_file,edi_test.tc_ows_file,edi_test.tc_owf_file,edi_test.gfe_file A,edi_test.gfe_file B ");
            strSql.AppendLine("WHERE ima01 = tc_ows005 AND tc_ows001 = tc_owf003 and tc_ows000 =tc_owf004 ");
            strSql.AppendLine("AND ima31 =trim(A.gfe01) AND ima25 =trim(B.gfe01)");
            strSql.AppendLine("AND(tc_owf002 is null OR tc_owf002 = '' OR tc_owf002 = ' ') ");
            strSql.AppendLine($"AND tc_owf001 ='{tc_owf001}' ");
            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString()))
            {
                List<Model.ImaModel> lst = new List<Model.ImaModel>();
                while (dr.Read())
                {
                    var model = new Model.ImaModel();
                    model.ima01 = DBHelper.GetString(dr["ima01"]);
                    model.ta_ima001 = DBHelper.GetString(dr["ta_ima001"]);
                    model.ima02 = DBHelper.GetString(dr["ima02"]);
                    model.ima31_fac = DBHelper.GetString(dr["ima31_fac"]);
                    model.ima06 = DBHelper.GetString(dr["ima06"]);
                    model.ima31 = DBHelper.GetString(dr["ima31"]);
                    model.ima31_dec = DBHelper.GetString(dr["ima31_dec"]);
                    model.ima25 = DBHelper.GetString(dr["ima25"]);
                    model.ima25_dec = DBHelper.GetString(dr["ima25_dec"]);
                    model.ima135 = DBHelper.GetString(dr["ima135"]);
                    model.ta_ima027 = DBHelper.GetString(dr["ta_ima027"]);
                    model.ta_ima028 = DBHelper.GetString(dr["ta_ima028"]);
                    model.ta_ima024 = DBHelper.GetString(dr["ta_ima024"]);
                    model.ta_ima025 = DBHelper.GetString(dr["ta_ima025"]);
                    model.ta_ima026 = DBHelper.GetString(dr["ta_ima026"]);
                    lst.Add(model);
                }
                return lst;


            }
        }




        /// <summary>
        /// 6.客户基本资料档
        /// </summary>
        /// <returns></returns>
        public List<Model.Occ_addModel> old_PrepareOcc_addData(string tc_owf001)
        {
            var strSql = new StringBuilder();
            strSql.AppendLine("SELECT DISTINCT tc_owr009,tc_owr005,occ01,occ242,occ294,occ304,occ296,occ306,occ29,occ30  ");
            strSql.AppendLine("FROM edi_test.occ_file,edi_test.tc_owr_file,edi_test.tc_owf_file ");
            strSql.AppendLine("WHERE occ01 = tc_owr015 AND tc_owr002 = tc_owf003 and tc_owr001 =tc_owf004 ");
            strSql.AppendLine("AND(tc_owf002 is null OR tc_owf002 = '' OR tc_owf002 = ' ') ");
            strSql.AppendLine($"AND tc_owf001 ='{tc_owf001}' ");
            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString()))
            {
                List<Model.Occ_addModel> lst = new List<Model.Occ_addModel>();
                while (dr.Read())
                {
                    var model = new Model.Occ_addModel();
                    model.tc_owr009 = DBHelper.GetString(dr["tc_owr009"]);
                    model.occ01 = DBHelper.GetString(dr["tc_owr005"]);
                    model.occ01 = DBHelper.GetString(dr["occ01"]);
                    var str = DBHelper.GetString(dr["occ242"]);
                    if (!string.IsNullOrWhiteSpace(str))
                    {
                        if (str.Length > 20)
                            model.occ242 = str.Substring(0, 20);
                        if (str.Length > 40)
                            model.occ243 = str.Substring(21, 20);
                    }
                    model.occ294 = DBHelper.GetString(dr["occ294"]);
                    model.occ304 = DBHelper.GetString(dr["occ304"]);
                    model.occ296 = DBHelper.GetString(dr["occ296"]);
                    model.occ306 = DBHelper.GetString(dr["occ306"]);
                    model.occ29 = DBHelper.GetString(dr["occ29"]);
                    model.occ30 = DBHelper.GetString(dr["occ30"]);
                    lst.Add(model);
                }
                return lst;
            }
        }

        public void old_EndUpdData(string l_tc_owr009,string filename)
        {
            var upd_owi_Sql = new StringBuilder();
            upd_owi_Sql.Clear();
            upd_owi_Sql.AppendLine("UPDATE edi_test.tc_owi_file SET tc_owi003='Y',tc_owi004='Y',tc_owi005='Y',tc_owidate=sysdate ");
            upd_owi_Sql.AppendLine("WHERE tc_owi001 = '" + l_tc_owr009.ToString() + "'");
            DBHelper.ExecuteSql(upd_owi_Sql.ToString());

            upd_owi_Sql.Clear();
            //上线时候改成2
            //upd_owi_Sql.AppendLine(" UPDATE EDI_TEST.tc_owr_file SET tc_owr019 = '2' ");
            upd_owi_Sql.AppendLine(" UPDATE EDI_TEST.tc_owr_file SET tc_owr019 = '1' ");
            upd_owi_Sql.AppendLine(" WHERE TC_OWR001|| TC_OWR002 IN ");
            upd_owi_Sql.AppendLine(" (SELECT tc_owf004 || tc_owf003 FROM tc_owf_file WHERE tc_owf001 = '" + filename.ToString() + "') ");
            upd_owi_Sql.AppendLine("  AND tc_owr019 = '1' AND tc_owr009 ='" + l_tc_owr009.ToString() + "'");
            DBHelper.ExecuteSql(upd_owi_Sql.ToString());

        }

    }
}
